/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package idea.model.dbmanager;


import static idea.model.dbmanager.SfoDbManager.FIRST_ELEMENT;
import idea.model.qnrlibrary.CustomContentItems;
import idea.model.qnrlibrary.CustomResponses;
import idea.model.qnrlibrary.DcAttributeGroup;
import idea.model.qnrlibrary.PaperAttributeGroup;
import idea.model.qnrlibrary.QuestionTitle;
import java.util.ArrayList;
import java.util.List;
import java.util.NoSuchElementException;
import javax.persistence.EntityManager;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;


/**
 *
 * @author renke002
 */
public class SfoDbManager
{

    /**
     * Position of the first element of a list.
     */
    public final static int FIRST_ELEMENT = 0;
    /**
     *
     */
    public final static String RESPONSE_QUESTION_NUMBER = ".#.";
    /**
     *
     */
    private final static EntityManager entityManager = Persistence.
            createEntityManagerFactory( "QnrLibraryPU" ).createEntityManager();


    /**
     * Given the 'content-id' and 'relative-index' values from a designer
     * memento's gids-designer:custom-content-cell-data field, this method will
     * query the database for the associated content (if any).
     * <p/>
     *
     * @param content_id
     * @param relative_index
     * @return The Cell Data Content element matching the parameters or 'null'
     * in none was found.
     * @throws IllegalArgumentException if the relative_index is not allowed.
     * @deprecated does not resolve a reference to another entry in the
     * question_title table.
     */
    @Deprecated
    public static String findCellDataContent( String content_id , String relative_index )
            throws IllegalArgumentException
    {
        /*
         * gids-designer:custom-content-cell-data
         *
         * When opening a question layout in the custom layout designer, the
         * first stage involves gathering up all of the bits and pieces of
         * content from the QUESTION_TITLE, CUSTOM_CONTENT_ITEMS and
         * CUSTOM_RESPONSES tables.
         *
         * Next, the content-id for each cell in the layout is matched to the
         * IDs gathered in the previous step. So, for example, if the content-id
         * matches a CUSTOM_CONTENT_ITEMS.ITEM_ID, and the relative-index is 4,
         * then we know that this cell contains a reference to the ITEM_WORDING
         * for that item, and so on.
         *
         * Labels, headers and reusable blocks work exactly the same way.
         * Pagemaster templates and regions work similarly, but the content is
         * looked up in different places.
         *
         */
        int relIndex = Integer.parseInt( relative_index );

        String content;

        /*
         * The content may be in any one of the four tables, and the only way to
         * know where it's located is to query each one of them.
         */

        // QUESTION_TITLE
        content = SfoDbManager.findQuestionTitle( content_id , relIndex );

        if ( null == content )
        {
            // there is no matching content_id in QUESTION_TITLE, so let's search
            // in CUSTOM_CONTENT_ITEMS
            content = SfoDbManager.
                    findCustomContentItem( content_id , relIndex );

            if ( null == content )
            {
                // there is no matching content_id in CUSTOM_CONTENT_ITEMS, so
                // let's search in CUSTOM_RESPONSES
                content = SfoDbManager.findCustomResponsesLabel( content_id ,
                        relIndex );

                if ( null == content )
                {
                    // there is no matching content_id in CUSTOM_RESPONSES, so
                    // let's search in REGION_DEFINITIONS
                    content = SfoDbManager.findRegionDefinitions( content_id ,
                            relIndex );

                    if ( null == content )
                    {
                        throw new NoSuchElementException(
                                "The content_id " + content_id + " was not found. " );
                    }
                }
            }
        }

        // QUESTION_TITLE
//        content = SfoDbManager.findQuestionTitle( content_id , relIndex );
//        content =
//                ( ( content == null ) ? SfoDbManager.findCustomContentItem( content_id , relIndex ) // CUSTOM_CONTENT_ITEMS.ITEM_ID
//                : ( content == null ) ? SfoDbManager.findCustomResponsesLabel( content_id , relIndex ) // CUSTOM_RESPONSES.RESPONSES_ID
//                : ( content == null ) ? SfoDbManager.findRegionDefinitions( content_id , relIndex ) // REGION_DEFINITIONS.REGION_DEF_ID
//                : null );
//
//        if ( null == content )
//        {
//            throw new NoSuchElementException( "The content_id " + content_id + " was not found. " );
//        }

        return content;
    }


    /**
     * Search the Custom Content Items table for an element with a matching
     * 'itemId'.
     * <p/>
     * @param itemId
     * @param relIndex
     * @return The Custom Content element matching the parameters or 'null' in
     * none was found.
     * @throws IllegalArgumentException if the relIndex is not allowed.
     */
    public static String findCustomContentItem( String itemId , int relIndex )
            throws IllegalArgumentException
    {
        List<CustomContentItems> cciList = entityManager
                .createNamedQuery( "CustomContentItems.findByItemId" ,
                CustomContentItems.class )
                .setParameter( "itemId" , itemId )
                .getResultList();

        if ( cciList.isEmpty() == false )
        {
            /*
             * The the value of the 'relative-index' variable determines which
             * column from the 'CUSTOM_CONTENT_ITEMS' table we need to return.
             *
             * CustomContentItems CUSTOM_CONTENT_ITEMS.ITEM_ID 
             * 3 CUSTOM_CONTENT_ITEMS.ITEM_NUMBER 
             * 4 CUSTOM_CONTENT_ITEMS.ITEM_WORDING 
             * 5 CUSTOM_CONTENT_ITEMS.HDR_COLUMN_REF 
             * 6 CUSTOM_CONTENT_ITEMS.HDR_KEYCODE
             *
             */
            CustomContentItems cci = cciList.get( FIRST_ELEMENT );

            String content =
                    ( ( 3 == relIndex ) ? cci.getItemNumber()
                    : ( 4 == relIndex ) ? cci.getItemWording()
                    : ( 5 == relIndex ) ? cci.getHdrColumnRef()
                    : ( 6 == relIndex ) ? cci.getHdrKeycode()
                    : null );
            //
            if ( content == null )
            {
                throw new IllegalArgumentException(
                        "The relative-index value (" + relIndex + " ) is not allowed for a Custom Response." );
            }
            return content;
        }
        /*
         * There is no element with a itemId that matches the one passed in as
         * parameter.
         */
        return null;
    }


    /**
     *
     * @param responseId
     */
    public static List<CustomResponses> findCustomResponses( String responseId )
    {
        return entityManager
                .createNamedQuery( "CustomResponses.findByResponsesId" ,
                CustomResponses.class )
                .setParameter( "responsesId" , responseId )
                .getResultList();
    }


    /**
     * Search the Custom Responses table for an element matching the parameters.
     * <p/>
     * @param responseId
     * @param relIndex
     * @return the Custom Responses element matching the parameters or 'null' in
     * none was found.
     * @throws IllegalArgumentException if the relIndex is not allowed.
     */
    public static String findCustomResponsesLabel( String responseId , int relIndex )
            throws IllegalArgumentException
    {
        //
        List<CustomResponses> crsList = entityManager
                .createNamedQuery( "CustomResponses.findByResponsesId" ,
                CustomResponses.class )
                .setParameter( "responsesId" , responseId )
                .getResultList();

        if ( crsList.isEmpty() == false )
        {
            CustomResponses crs = crsList.get( SfoDbManager.FIRST_ELEMENT );
            String content;
            /*
             * The the value of the 'relative-index' variable determines which
             * column from the 'CUSTOM_RESPONSES' table we need to return.
             *
             * CUSTOM_RESPONSES.RESPONSES_ID 
             * 1 reference to the response area itself 
             * 4 CUSTOM_RESPONSES.RESPONSE_LABEL 
             *      + CUSTOM_RESPONSES.RESPONSE_INSTRUCTION (concatenated) 
             * 6 CUSTOM_RESPONSES.KEYCODE
             *
             */
            switch ( relIndex )
            {
                case 1:
                    // 1 reference to the response area itself 
                    content = "...";
                    break;
                case 4:
                    // 4 RESPONSE_LABEL + RESPONSE_INSTRUCTION (concatenated) 
                    String responseLabel =
                            ( crs.getResponseLabel() == null )
                            ? "" : crs.getResponseLabel();

                    String responseInstructions =
                            ( crs.getResponseInstruction() == null )
                            ? "" : crs.getResponseInstruction();

                    content = responseLabel + responseInstructions;
                    break;
                case 6:
                    // 6 CUSTOM_RESPONSES.KEYCODE
                    content = crs.getKeycode();
                    break;
                default:
                    throw new IllegalArgumentException(
                            "The relative-index value (" + relIndex + " ) is not allowed for a Custom Response." );
            }
            return content;
        }
        /*
         * There is no element with a responseId that matches the one passed in
         * as parameter.
         */
        return null;
    }


    /**
     * Find all the Data Capture Attribute Group objects whose primary ID
     * matches the parameter.
     * <p/>
     * @param dcAttrGroupId Data Capture Attribute Group primary ID.
     * @return List of Data Capture Attribute Group objects.
     */
    public static List<DcAttributeGroup> findDcAttributeGroup( String dcAttrGroupId )
    {
        // "DcAttributeGroup.findByAgId"
        return entityManager
                .createNamedQuery( "DcAttributeGroup.findByAgId" ,
                DcAttributeGroup.class )
                .setParameter( "agId" , dcAttrGroupId )
                .getResultList();
    }


    /**
     *
     * @param contentId
     */
    public static List<List<String>> findHeaders( String contentId )
    {
        List<CustomContentItems> customContentItems = QueryManager.
                getCustomContentItemsList( contentId );

        if ( customContentItems != null && customContentItems.isEmpty() == false )
        {
            List<List<String>> cciElements = new ArrayList<>();

            ArrayList<String> cciValues = null;

            for ( CustomContentItems cci : customContentItems )
            {
                cciValues = new ArrayList<>();

                cciValues.add( cci.getItemSequence().intValue() + "" );
                cciValues.add( cci.getItemType() );
                cciValues.add( cci.getItemNumber() );
                cciValues.add( cci.getItemWording() );

                cciElements.add( cciValues );
            }

            return cciElements;
        }
        // the list was empty or null, so there is nothing to return.
        return null;
    }


    /**
     * Find all the Paper Capture Attribute Group objects whose primary ID
     * matches the parameter.
     * <p/>
     * @param paperAttrGroupId Paper Capture Attribute Group primary ID.
     * @return List of Paper Capture Attribute Group objects.
     */
    public static List<PaperAttributeGroup> findPaperAttributeGroup( String paperAttrGroupId )
    {
        // "PaperAttributeGroup.findByAgId"
        return entityManager
                .createNamedQuery( "PaperAttributeGroup.findByAgId" ,
                PaperAttributeGroup.class )
                .setParameter( "agId" , paperAttrGroupId )
                .getResultList();
    }


    /**
     * Search the Question Title table for an element matching the parameters.
     * <p/>
     * @param questId
     * @param relIndex
     * @return the Question Title element matching the parameters or 'null' in
     * none was found.
     * @throws IllegalArgumentException if the relIndex is not allowed.
     */
    public static String findQuestionTitle( String questId , int relIndex )
            throws IllegalArgumentException
    {
        //TypedQuery<QuestionTitle> tq = entityManager.createQuery( "QuestionTitle.findByQuestId" , QuestionTitle.class );
        TypedQuery<QuestionTitle> tq = entityManager.createNamedQuery(
                "QuestionTitle.findByQuestId" , QuestionTitle.class );
        //TypedQuery<QuestionTitle> tq = entityManager.createQuery( "SELECT q FROM QuestionTitle q WHERE q.questId = :questId" , QuestionTitle.class );

        tq.setParameter( "questId" , questId );
        //
        List<QuestionTitle> resultList = tq.getResultList();

        if ( resultList.isEmpty() == false )
        {
            String content;
            QuestionTitle qt = resultList.get( SfoDbManager.FIRST_ELEMENT );
            /*
             * The the value of the 'relative-index' variable determines which
             * column from the 'QUESTION_TITLE' table we need to return.
             *
             * QUESTION_TITLE.QUEST_ID 
             * 0 reference to a header or reusable block in its entirety 
             * 2 QUESTION_TITLE.TITLE 
             * 4 QUESTION_TITLE.QUEST_WORDING 
             * 7 question number (replaceable token, not explicitly present in content)
             *
             */
            switch ( relIndex )
            {
                case 0:
                    // 0 reference to a header or reusable block in its entirety 
                    content = qt.getHeaderId();
                    // if content == null -> CUSTOM_HEADER_REB_ASSN!!!
                    break;
                case 2:
                    // 2 QUESTION_TITLE.TITLE 
                    content = qt.getTitle();
                    break;
                case 4:
                    // 4 QUESTION_TITLE.QUEST_WORDING 
                    content = qt.getQuestWording();
                    break;
                case 7:
                    // 7 question number (replaceable token, not explicitly present in content)
//                    content = qt.toString();
                    content = SfoDbManager.RESPONSE_QUESTION_NUMBER;
                    break;
                default:
                    throw new IllegalArgumentException(
                            "The relative-index value ( " + relIndex + " ) is not allowed for a Question Title." );
            }
            return content;
        }
        /*
         * There is no element with a questId that matches the one passed in as
         * parameter.
         */
        return null;
    }


    /**
     *
     * @param content_id
     * @param relIndex
     * @return the Region Definitions element matching the parameters or 'null'
     * in none was found.
     * @throws IllegalArgumentException if the relIndex is not allowed.
     */
    public static String findRegionDefinitions( String content_id , int relIndex )
    {
        /*
         *
         * REGION_DEFINITIONS.REGION_DEF_ID 8 reference to computed page/face
         * barcode (ACS/Decennial only) 9 reference to computed page/face page
         * number (ACS/Decennial only)
         *
         */
        String content = null;
        // get DB connection
        // find stuff.
        return content;
    }

}
